<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Document</title>
    <script lang="javascript" src="xlsx.full.min.js"></script>
    <script lang="javascript" src="vueGlobal.js"></script>
    <!-- <script src="https://unpkg.com/vue@next"></script>
    <script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script> -->
    <!-- <script src="https://unpkg.com/dayjs/dayjs.min.js"></script>
    <script src="https://unpkg.com/dayjs/plugin/customParseFormat.js"></script>
    <script src="https://unpkg.com/dayjs/plugin/weekday.js"></script>
    <script src="https://unpkg.com/dayjs/plugin/localeData.js"></script>
    <script src="https://unpkg.com/dayjs/plugin/weekOfYear.js"></script>
    <script src="https://unpkg.com/dayjs/plugin/weekYear.js"></script>
    <script src="https://unpkg.com/dayjs/plugin/advancedFormat.js"></script>
    <script src="https://unpkg.com/dayjs/plugin/quarterOfYear.js"></script>
    <link
      rel="stylesheet"
      type="text/css"
      href="./ant/antd.min.css"
      media="screen"
    />
    <script lang="javascript" src="./ant/antd.js"></script> -->
  </head>

  <body>
    <div style="width: 100%" id="app">
      <!-- <a-upload
        v-model:file-list="fileList"
        multiple
        name="file"
        @change="handleChange"
      >
        <a-button> {{name}} </a-button>
      </a-upload> -->
      导入默认表：<input
        type="file"
        multiple
        @change="(e)=>handleChange(e,'default')"
      />
      导入明细表：<input
        :disable="isDefaultComplete"
        type="file"
        multiple
        @change="handleChange"
      />
      <!-- <button @click="onGenarateSheet">生成新excel</button> -->
    </div>
  </body>
</html>
<script type="module">
  const uniCodeMap = {
    lx: "\u7c7b\u578b", // 类型
    jdmc: "\u57fa\u5730\u540d\u79f0", // 基地名称
    ywlx: "\u4e1a\u52a1\u7c7b\u578b", // 业务类型
    ljcprc: "\u7d2f\u8ba1\u53c2\u57f9\u4eba\u6b21", // 累计参培人次
    ljcpnx: "\u7d2f\u8ba1\u53c2\u57f9\u5973\u6027", //累计参培女性
    tgkhrc: "\u901a\u8fc7\u8003\u6838\u4eba\u6b21", //通过考核人次
    ljtgkhnxrc: "\u7d2f\u8ba1\u901a\u8fc7\u8003\u6838\u5973\u6027\u4eba\u6b21", //累计通过考核女性人次
    ljcprc_2: "\u7d2f\u8ba1\u53c2\u57f9\u4eba\u6b21\u005f\u0032", // 累计参培人次_2
    ljcpnx_2: "\u7d2f\u8ba1\u53c2\u57f9\u5973\u6027\u005f\u0032", // 累计参培女性_2
    tgkhrc_2: "\u901a\u8fc7\u8003\u6838\u4eba\u6b21\u005f\u0032", //通过考核人次_2
    ljtgkhnxrc_2:
      "\u7d2f\u8ba1\u901a\u8fc7\u8003\u6838\u5973\u6027\u4eba\u6b21\u005f\u0032", //累计通过考核女性人次_2
    ywyrs: "\u4e1a\u52a1\u5458\u4eba\u6570", //业务员人数
    ywynxrs: "\u4e1a\u52a1\u5973\u6027\u4eba\u6570", //业务女性人数
    bzrz: "\u672c\u5468\u5165\u804c", //本周入职
    bzlz: "\u672c\u5468\u79bb\u804c", //本周离职
    glcrs: "\u7ba1\u7406\u5c42\u4eba\u6570", //管理层人数
    glcnxrs: "\u7ba1\u7406\u5973\u6027\u4eba\u6570", //管理女性人数
    zzhj: "\u5728\u804c\u5408\u8ba1", // 在职合计
    zznxhj: "\u5728\u804c\u5973\u6027\u5408\u8ba1", //在职女性合计
    pxz: "\u57f9\u8bad\u4e2d", //培训中
    hzb: "\u6c47\u603b\u8868", //汇总表
    yjywlx: "\u4e00\u7ea7\u4e1a\u52a1\u7c7b\u578b", // 一级业务类型
    rjywlx: "\u4e8c\u7ea7\u4e1a\u52a1\u7c7b\u578b", //二级业务类型
    xingbie: "\u6027\u522b", //性别
    nv: "\u5973", //女
    khtgsgrq: "\u8003\u6838\u901a\u8fc7\u4e0a\u5c97\u65e5\u671f", // 考核通过上岗日期
    zhiwei: "\u804c\u4f4d", //职位
    ywy: "\u4e1a\u52a1\u5458", //业务员
    lzrq: "\u79bb\u804c\u65e5\u671f", //离职日期
    gly: "\u7ba1\u7406\u5458", //管理员
    ygzt: "\u5458\u5de5\u72b6\u6001", //员工状态
    zaizhi: "\u5728\u804c", //在职
    hzsj: "\u6c47\u603b\u6570\u636e", //汇总数据
    beizhu: "\u5907\u6ce8\uff08\u57f9\u8bad\u4e2d\uff09", //备注（培训中）
    lizhiyuanyin: "\u79bb\u804c\u539f\u56e0", // 离职原因
  };
  Object.assign(window, Vue);
  const vue3Composition = {
    setup() {
      // 变量部分
      const data = reactive({});
      const dataRef = toRefs(data);
      const sheetContent = ref([]);
      const defaultSheetContent = ref([]);
      const summaryData = ref([]);
      const isDefaultComplete = ref(false);
      const summaryItemObj = {
        [uniCodeMap.lx]: "",
        [uniCodeMap.jdmc]: "",
        [uniCodeMap.ywlx]: "",
        [uniCodeMap.ljcprc]: 0,
        [uniCodeMap.ljcpnx]: 0,
        [uniCodeMap.tgkhrc]: 0, //通过考核人次
        [uniCodeMap.ljtgkhnxrc]: 0, //累计通过考核女性人次
        [uniCodeMap.ywyrs]: 0, //业务员人数
        [uniCodeMap.ywynxrs]: 0, //业务女性人数
        [uniCodeMap.bzrz]: 0, //本周入职
        [uniCodeMap.bzlz]: 0, //本周离职
        [uniCodeMap.glcrs]: 0, //管理层人数
        [uniCodeMap.glcnxrs]: 0, //管理女性人数
        [uniCodeMap.zzhj]: 0, //在职合计
        [uniCodeMap.zznxhj]: 0, //在职女性合计
        [uniCodeMap.pxz]: 0, //培训中
        [uniCodeMap.lizhiyuanyin]: "", // 离职原因
      };
      // 函数部分
      const methods = reactive({
        handleChange: async (e, isDefault) => {
          // await readWorkbookFromLocalFile(e.file.originFileObj); // antd
          await readWorkbookFromLocalFile(e.target.files[0], isDefault);
        },
      });
      // 生成汇总表
      const onGenarateSheet = () => {
        const sheet = XLSX.utils.json_to_sheet(summaryData.value);
        const fileName = `${uniCodeMap.hzb}.xlsx`; // 汇总表
        const blob = sheet2blob(sheet, fileName);
        let url = window.URL.createObjectURL(blob); // 创建 url 并指向 blob
        let a = document.createElement("a");
        a.href = url;
        a.download = fileName;
        a.click();
        window.URL.revokeObjectURL(url); // 释放该 url
      };
      // function handleDefaultSheetContent() {}
      function handleSheetContent() {
        const detailData = sheetContent.value;
        const onlyInfoList = Array.from(
          new Set(
            detailData.map(
              (item) =>
                `${item[uniCodeMap.yjywlx]}_${item[uniCodeMap.jdmc]}_${
                  item[uniCodeMap.rjywlx]
                }` // 一级业务类型_基地名称_二级业务类型
            )
          )
        );
        summaryData.value = onlyInfoList.map((onlyInfo) => {
          const [type, baseName, businessType] = onlyInfo.split("_");
          const defaultItem = defaultSheetContent.value.find((item) => {
            return (
              item[uniCodeMap.lx] === type && // 类型 == 一级业务类型
              item[uniCodeMap.jdmc] === baseName && // 基地名称 == 基地名称
              item[uniCodeMap.ywlx] === businessType // 业务类型 == 二级业务类型
            );
          });
          return {
            ...summaryItemObj,
            [uniCodeMap.lx]: type, // 类型
            [uniCodeMap.jdmc]: baseName, // 基地名称
            [uniCodeMap.ywlx]: businessType, //业务类型
            // 四项默认值
            ...(defaultItem
              ? {
                  [uniCodeMap.ljcprc]: defaultItem[uniCodeMap.ljcprc_2] || 0, // 累计参培人次
                  [uniCodeMap.ljcpnx]: defaultItem[uniCodeMap.ljcpnx_2] || 0, // 累计参培女性
                  [uniCodeMap.tgkhrc]: defaultItem[uniCodeMap.tgkhrc_2] || 0, // 通过考核人次
                  [uniCodeMap.ljtgkhnxrc]:
                    defaultItem[uniCodeMap.ljtgkhnxrc_2] || 0, // 累计通过考核女性人次
                }
              : {}),
          };
        });
        for (let detailItem of detailData) {
          for (let summaryItem of summaryData.value) {
            if (
              summaryItem[uniCodeMap.lx] === detailItem[uniCodeMap.yjywlx] && // 类型 == 一级业务类型
              summaryItem[uniCodeMap.jdmc] === detailItem[uniCodeMap.jdmc] && // 基地名称 == 基地名称
              summaryItem[uniCodeMap.ywlx] === detailItem[uniCodeMap.rjywlx] // 业务类型 == 二级业务类型
            ) {
              const isFemale = detailItem[uniCodeMap.xingbie] === uniCodeMap.nv; // 是否为女性
              //处理数据
              // 累计参培人次
              summaryItem[uniCodeMap.ljcprc] += 1;
              if (isFemale) {
                summaryItem[uniCodeMap.ljcpnx] += 1;
              }
              // 通过考核人次（有考核通过上岗日期）
              if (detailItem[uniCodeMap.khtgsgrq]) {
                summaryItem[uniCodeMap.tgkhrc] += 1;
                if (isFemale) {
                  summaryItem[uniCodeMap.ljtgkhnxrc] += 1;
                }
              }
              // 本周入职、离职
              const entryTime = detailItem[uniCodeMap.khtgsgrq];
              const departTime = detailItem[uniCodeMap.lzrq];
              if (
                entryTime &&
                isDateInWeek(transferExcelTimeToDate(entryTime))
              ) {
                summaryItem[uniCodeMap.bzrz] += 1;
              }
              if (
                departTime &&
                entryTime &&
                isDateInWeek(transferExcelTimeToDate(departTime))
              ) {
                summaryItem[uniCodeMap.lizhiyuanyin] +=
                  detailItem[uniCodeMap.beizhu] + " / ";
                summaryItem[uniCodeMap.bzlz] += 1;
              }
              // 培训中
              if (detailItem[uniCodeMap.ygzt] === uniCodeMap.pxz) {
                summaryItem[uniCodeMap.pxz] += 1;
              }
              const isOnjob = detailItem[uniCodeMap.ygzt] === uniCodeMap.zaizhi; // 是否在职
              if (isOnjob) {
                // 在职合计
                summaryItem[uniCodeMap.zzhj] += 1;
                if (isFemale) {
                  summaryItem[uniCodeMap.zznxhj] += 1;
                }
                // 业务员
                if (detailItem[uniCodeMap.zhiwei] === uniCodeMap.ywy) {
                  summaryItem[uniCodeMap.ywyrs] += 1;
                  if (isFemale) {
                    summaryItem[uniCodeMap.ywynxrs] += 1;
                  }
                }
                // 管理员
                if (detailItem[uniCodeMap.zhiwei] === uniCodeMap.gly) {
                  summaryItem[uniCodeMap.glcrs] += 1;
                  if (isFemale) {
                    summaryItem[uniCodeMap.glcnxrs] += 1;
                  }
                }
              }
            }
          }
        }
        console.log(`${uniCodeMap.hzsj}=>`, summaryData.value);
        onGenarateSheet();
      }
      // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
      function sheet2blob(sheet, sheetName) {
        sheetName = sheetName || "sheet1";
        var workbook = {
          SheetNames: [sheetName],
          Sheets: {},
        };
        workbook.Sheets[sheetName] = sheet;
        // 生成excel的配置项
        var wopts = {
          bookType: "xlsx", // 要生成的文件类型
          bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
          type: "binary",
        };
        var wbout = XLSX.write(workbook, wopts);
        var blob = new Blob([s2ab(wbout)], {
          type: "application/octet-stream",
        });
        // 字符串转ArrayBuffer
        function s2ab(s) {
          var buf = new ArrayBuffer(s.length);
          var view = new Uint8Array(buf);
          for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
          return buf;
        }
        return blob;
      }
      //读取本地Excel表格
      function readWorkbookFromLocalFile(file, isDefault) {
        var fileReader = new FileReader();
        fileReader.readAsBinaryString(file);
        return new Promise(function (resolve, reject) {
          fileReader.onload = function (ev) {
            isDefaultComplete.value = true;
            try {
              var data = ev.target.result;
              var workbook = XLSX.read(data, {
                type: "binary",
              }); // 以二进制流方式读取得到整份excel表格对象
              var fromTo = "";
              if (isDefault) {
                defaultSheetContent.value = [];
                for (var sheet in workbook.Sheets) {
                  // 遍历每张表读取
                  if (workbook.Sheets.hasOwnProperty(sheet)) {
                    const worksheet = workbook.Sheets[sheet];
                    fromTo = worksheet["!ref"]; // 确定excel的范围
                    defaultSheetContent.value.push(
                      ...XLSX.utils.sheet_to_json(worksheet, { range: 1 })
                    );
                    // break; // 如果只取第一张表，就取消注释这行 [{key:value}, {modelKey:modelValue}]
                  }
                }
                console.log("默认表=>", defaultSheetContent.value);
                alert("默认表解析成功！");
              } else {
                sheetContent.value = [];
                for (var sheet in workbook.Sheets) {
                  // 遍历每张表读取
                  if (workbook.Sheets.hasOwnProperty(sheet)) {
                    const worksheet = workbook.Sheets[sheet];
                    fromTo = worksheet["!ref"]; // 确定excel的范围
                    sheetContent.value.push(
                      ...XLSX.utils.sheet_to_json(worksheet)
                    );
                    // break; // 如果只取第一张表，就取消注释这行 [{key:value}, {modelKey:modelValue}]
                  }
                }
                handleSheetContent();
              }
            } catch (e) {
              console.log(e);
            } finally {
              isDefaultComplete.value = false;
            }
          };
        });
      }
      function transferExcelTimeToDate(serial) {
        var utc_days = Math.floor(serial - 25569);
        var utc_value = utc_days * 86400;
        var date_info = new Date(utc_value * 1000);
        var fractional_day = serial - Math.floor(serial) + 0.0000001;
        var total_seconds = Math.floor(86400 * fractional_day);
        var seconds = total_seconds % 60;
        total_seconds -= seconds;
        var hours = Math.floor(total_seconds / (60 * 60));
        var minutes = Math.floor(total_seconds / 60) % 60;
        return new Date(
          date_info.getFullYear(),
          date_info.getMonth(),
          date_info.getDate(),
          hours,
          minutes,
          seconds
        );
      }
      const isDateInWeek = (date) => {
        // 获取当前日期
        const currentDate = new Date();
        // 获取本周的开始日期
        const startOfWeek = new Date(
          currentDate.getFullYear(),
          currentDate.getMonth(),
          currentDate.getDate() - currentDate.getDay() - 1
        );
        // 获取本周的结束日期
        const endOfWeek = new Date(
          currentDate.getFullYear(),
          currentDate.getMonth(),
          currentDate.getDate() + (5 - currentDate.getDay())
        );
        return date >= startOfWeek && date <= endOfWeek;
      };

      return {
        ...dataRef,
        sheetContent,
        ...methods,
      };
    },
  };
  const app = createApp(vue3Composition).mount("#app"); //初始化
</script>
